package com.apes.scm.income.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.income.model.Income;
import com.apes.scm.income.model.IncomeItem;
import com.apes.scm.masterdata.party.model.PartyRole;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 收入
 *
 * @author wuq
 * @create 2018-11-27 10:40
 */
public interface IncomeItemRepository extends CustomRepository<IncomeItem, String> {

    List<IncomeItem> findAllByDoneAndIncome_StateAndIncome_Contract_Id(boolean isDone, String state, String contractId);

    @Query(value = "select T from IncomeItem T left join T.income f where T.done = ?1 and f.state=?2 and f.partner.id = ?3 and T.tax.id = ?4 and f.company.id=?5")
    List<IncomeItem> findAllBySupplierAndCompany(boolean isDone, String state, String supplierId, Long taxId, String companyId);

    /**
     * fetch data by partner Id and dept Id
     *
     * @param partnerId partner id
     * @param deptId    dept Id
     * @param done      done
     * @return Page<Map < String, Object>>
     */
    @Query(
            value = "select new map(t.id as id ,f.id as incomeId, g.id as incomeTypeId ,g.name as incomeTypeName ,t.incomeAmount as incomeAmount," +
                    " t.recoveryAmount as recoveryAmount ,t.freezeAmount as freezeAmount, m.id as  companyId,m.party.name as companyName , " +
                    " to_char(t.startDate,'yyyy-MM-dd') as reconcileBeginDate ,to_char(t.endDate,'yyyy-MM-dd') as reconcileEndDate ,to_char(f.approveDate,'yyyy-MM-dd') as countDate," +
                    "c.id as inComeCategoryId ,c.description as inComeCategoryName ) " +
                    "from IncomeItem t left join t.income f left join t.incomeType g left join f.inComeCategory c left join f.company m " +
                    "where  f.partner.id = ?1" +
                    " and f.dept.id = ?2  and t.done = ?3 and f.state ='approve'"
    )
    List<Map<String, Object>> findInvoiceItemsByPartnerIdAndDept(String partnerId, String deptId, boolean done);

    /**
     * fetch data by partner Id and dept Id
     *
     * @param partnerId          partner id
     * @param companyId          company Id
     * @param contractTypeId     contract type Id
     * @param done               done
     * @param reconcileBeginDate start date
     * @param reconcileEndDate   end date
     * @return Page<Map < String, Object>>
     */
    @Query(
            value = "select t from IncomeItem t  " +
                    "where  t.income.partner.id = ?1" +
                    " and t.income.company.id = ?2  and t.done = ?3 and t.income.state ='approve' " +
                    " and t.income.approveDate >= ?4 and t.income.approveDate<=?5 and t.income.contract.contractType.id=?6"
    )
    List<IncomeItem> findInvoiceItemsByPartnerIdAndCompanyId(String partnerId, String companyId, boolean done,
                                                             Date reconcileBeginDate, Date reconcileEndDate, String contractTypeId);

    @Query("select new map(a.id as incomeItemId, b.id as incomeId, g.id as incomeTypeId, g.name as incomeTypeName, " +
            " a.startDate as startDate, a.endDate as endDate, CONCAT(CONCAT(b.id, '-'), a.id ) as uniqueKey, " +
            " a.incomeAmount as incomeAmount, a.recoveryAmount as recoveryAmount, a.freezeAmount as freezeAmount, " +
            " a.incomeAmount - a.recoveryAmount - a.freezeAmount as needIncomeAmount,  " +
            " c.id as inComeCategoryId, c.description as inComeCategoryName, " +
            " b.partner.id as partnerId, b.partner.party.name as partnerName, " +
            " b.company.id as companyId, b.company.party.name as companyName, " +
            " b.cooperation.id as cooperationId, b.cooperation.name as cooperationName," +
            " b.dept.id as deptId, b.dept.name as deptName) " +
            " from IncomeItem a left join Income b on a.income.id = b.id" +
            "      left join a.incomeType g " +
            "      left join b.inComeCategory c " +
            "  where b.state = 'approve' and b.approveDate >= :approveStartDate and b.approveDate <= :approveEndDate " +
            " and c.id <> 'rebate' " +
            " and b.partner.id = :partnerId and a.incomeAmount - a.freezeAmount - a.recoveryAmount > 0 ")
    Page<Map<String, Object>> findAllForCombinePayment(@Param("partnerId") String partnerId,
                                                       @Param("approveStartDate") Date approveStartDate,
                                                       @Param("approveEndDate") Date approveEndDate, Pageable pageable);


    @Query("select new map(a.id as incomeItemId, b.id as incomeId, g.id as incomeTypeId, g.name as incomeTypeName, " +
            " a.startDate as startDate, a.endDate as endDate, CONCAT(CONCAT(b.id, '-'), a.id ) as uniqueKey, " +
            " a.incomeAmount as incomeAmount, a.recoveryAmount as recoveryAmount, a.freezeAmount as freezeAmount, " +
            " a.incomeAmount - a.recoveryAmount - a.freezeAmount as needIncomeAmount,  " +
            " c.id as inComeCategoryId, c.description as inComeCategoryName, " +
            " b.partner.id as partnerId, b.partner.party.name as partnerName, " +
            " b.company.id as companyId, b.company.party.name as companyName,b.carLicense as carLicense, " +
            " b.cooperation.id as cooperationId, b.cooperation.name as cooperationName," +
            " b.dept.id as deptId, b.dept.name as deptName) " +
            " from IncomeItem a left join Income b on a.income.id = b.id" +
            "      left join a.incomeType g " +
            "      left join b.inComeCategory c " +
            "  where b.state = 'approve' and b.approveDate >= :approveStartDate and b.approveDate <= :approveEndDate " +
            " and b.partner.id = :partnerId and b.company.id= :companyId and  a.freezeAmount + a.recoveryAmount != a.incomeAmount  order by a.startDate ")
    Page<Map<String, Object>> findAllForSupplierIncomePay(@Param("partnerId") String partnerId, String companyId,
                                                          @Param("approveStartDate") Date approveStartDate,
                                                          @Param("approveEndDate") Date approveEndDate, Pageable pageable);


    @Query("select new map(b.incomeAmount as incomeAmount,b.freezeAmount as freezeAmount," +
            "b.recoveryAmount as recoveryAmount,a.id as incomeId,b.incomeType AS incomeType," +
            "b.tax AS tax,(b.incomeAmount * b.tax.amt * 0.1 * 0.1) AS amtTax,b.id AS incomeItemId)" +
            " from Income a,IncomeItem b where a.partner.id = ?1 and a.id = b.income and b.done = 0 " +
            " and a.state = 'approve' and a.inComeCategory = 'rebate'")
    List<Map<String, Object>> partnerId(@Param("partnerId") String partnerId);

    @Query("select t from IncomeItem t where t.id = :id")
    IncomeItem byId(@Param("id") String id);

    /**
     * fetch data by date and tenant id and state
     *
     * @param companyId company id
     * @return List<IncomeItem>
     */
    @Query("select T from IncomeItem T left join T.income E left join T.incomeType I where E.company.id =:companyId and (E.approveDate <= :approveDate Or T.endDate <= :approveDate)  and E.state = 'approve'" +
            "and ((T.freezeAmount+T.recoveryAmount) <> T.incomeAmount  OR (T.incomeAmount = 0 and T.freezeAmount = 0 and T.recoveryAmount = 0 and T.done = false))  order by I.id,E.createDate ")
    List<IncomeItem> findAllByCompanys(String companyId,Date approveDate);

    @Query(
            value = "select new map(t.id as id ,f.id as incomeId, g.id as incomeTypeId ,g.name as incomeTypeName ,t.incomeAmount as incomeAmount," +
                    " t.recoveryAmount as recoveryAmount ,t.freezeAmount as freezeAmount,t.freezeAmount + t.recoveryAmount as settleAmount ,m.id as  companyId,m.party.name as companyName , " +
                    " to_char(t.startDate,'yyyy-MM-dd') as reconcileBeginDate ,to_char(t.endDate,'yyyy-MM-dd') as reconcileEndDate ,to_char(f.approveDate,'yyyy-MM-dd') as countDate," +
                    "c.id as inComeCategoryId ,c.description as inComeCategoryName ) " +
                    "from IncomeItem t left join t.income f left join t.incomeType g left join f.inComeCategory c left join f.company m " +
                    "where f.company.id = :companyId and f.partner.id = :partnerId and f.cooperation.id = :cooperation and t.done = :done and f.state ='approve' and f.inComeCategory.id in (:category) and (t.incomeAmount - (t.freezeAmount + t.recoveryAmount)) <> 0"
    )
    List<Map<String, Object>> findInvoiceItemsByPartnerIdAndCategory(@Param("partnerId") String partnerId, @Param("companyId") String companyId, @Param("done") boolean done,@Param("cooperation") String cooperation,@Param("category") List category);
}
